﻿Imports System.Data.SqlClient

Module Utilities

    Public Function GetFinancialSettingsAccNo(ByVal Item As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From FinSettings Where Item=@Item", cnn4)
            Dim ItemAccNo As String

            cnn4.Open()
            cmd.Parameters.AddWithValue("@Item", Item)
            ItemAccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return ItemAccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetRetailDiscountAccNo(ByVal Item As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From FinSettings Where Item=@Item", cnn4)
            Dim ItemAccNo As String

            cnn4.Open()
            cmd.Parameters.AddWithValue("@Item", Item)
            ItemAccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return ItemAccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function ValidateDate(ByVal TransDate As Date) As Boolean
        Try
            Dim LockDate As Date
            Dim cmd As New SqlCommand("Select LockDate From LockDate", cnn1)

            cnn1.Open()
            LockDate = CDate(cmd.ExecuteScalar)
            cnn1.Close()

            If TransDate <= LockDate Then
                Return False
            Else
                Return True
            End If
        Catch ex As Exception
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
        End Try
    End Function

    Public Function GetAccGroupNo() As Double
        Try
            Dim cmd As New SqlCommand("Select IsNull(Count(Distinct Acc1),0) From Acc Where Acc2 Is Null", cnn4)
            Dim AccGroupNo As Double

            cnn4.Open()
            AccGroupNo = CDbl(cmd.ExecuteScalar) + 1
            cnn4.Close()

            Return AccGroupNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetAccName(ByVal AccNo As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(Acc4,N'') From Acc Where AccNo=@AccNo", cnn4)
            Dim AccName As String

            cnn4.Open()

            'Add values
            cmd.Parameters.AddWithValue("@AccNo", AccNo)

            AccName = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return AccName
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetAccNo(ByVal Acc1 As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From Acc Where Acc1=@Acc1 and Acc2=N''", cnn4)
            Dim AccNo As String

            cnn4.Open()

            'Add values
            cmd.Parameters.AddWithValue("@Acc1", Acc1)

            AccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return AccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetAccNo(ByVal Acc1 As String, ByVal Acc2 As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From Acc Where Acc1=@Acc1 and Acc2=@Acc2 and Acc3=N''", cnn4)
            Dim AccNo As String

            cnn4.Open()

            'Add values
            cmd.Parameters.AddWithValue("@Acc1", Acc1)
            cmd.Parameters.AddWithValue("@Acc2", Acc2)

            AccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return AccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetAccNo(ByVal Acc1 As String, ByVal Acc2 As String, ByVal Acc3 As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From Acc Where Acc1=@Acc1 and Acc2=@Acc2 and Acc3=@Acc3 " & _
                                      " and Acc4=N''", cnn4)
            Dim AccNo As String

            cnn4.Open()

            'Add values
            cmd.Parameters.AddWithValue("@Acc1", Acc1)
            cmd.Parameters.AddWithValue("@Acc2", Acc2)
            cmd.Parameters.AddWithValue("@Acc3", Acc3)

            AccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return AccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetAccNo(ByVal Acc1 As String, ByVal Acc2 As String, ByVal Acc3 As String, ByVal Acc4 As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From Acc Where Acc1=@Acc1 and Acc2=@Acc2 and Acc3=@Acc3 " & _
                                     "and Acc4=@Acc4", cnn4)
            Dim AccNo As String

            cnn4.Open()

            'Add values
            cmd.Parameters.AddWithValue("@Acc1", Acc1)
            cmd.Parameters.AddWithValue("@Acc2", Acc2)
            cmd.Parameters.AddWithValue("@Acc3", Acc3)
            cmd.Parameters.AddWithValue("@Acc4", Acc4)

            AccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return AccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetAccNo(ByVal Acc1 As String, ByVal Acc2 As String, ByVal Acc3 As String, ByVal Acc4 As String, ByVal Acc5 As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(AccNo,N'') From Acc Where Acc1=@Acc1 and Acc2=@Acc2 and Acc3=@Acc3 " & _
                                    "and Acc4=@Acc4 and Acc5=@Acc5", cnn4)
            Dim AccNo As String

            cnn4.Open()

            'Add values
            cmd.Parameters.AddWithValue("@Acc1", Acc1)
            cmd.Parameters.AddWithValue("@Acc2", Acc2)
            cmd.Parameters.AddWithValue("@Acc3", Acc3)
            cmd.Parameters.AddWithValue("@Acc4", Acc4)
            cmd.Parameters.AddWithValue("@Acc5", Acc5)

            AccNo = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return AccNo
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Function ValidateAccNo(ByVal AccNo As String) As Boolean
        Try
            Dim cmd As New SqlCommand("Select IsNull(Count(*),0) From Acc Where AccNo=N'" & AccNo & "'", cnn)
            Dim X As Integer

            cnn.Open()
            X = CInt(cmd.ExecuteScalar)
            cnn.Close()

            If X > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetBalanceAcc(ByVal Acc1 As String) As Double
        Try
            Dim cmd As New SqlCommand("Select Case When Sum(TotalIn)-Sum(TotalOut) Is Null Then 0 Else Sum(TotalIn)-Sum(TotalOut) End " & _
                                      "From Transactions Where Acc1=N'" & Acc1 & "'", cnn4)
            Dim Balance As Double

            cnn4.Open()
            Balance = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Balance
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetBalanceAcc(ByVal Acc1 As String, ByVal Acc2 As String) As Double
        Try
            Dim cmd As New SqlCommand("Select Case When Sum(TotalIn)-Sum(TotalOut) Is Null Then 0 Else Sum(TotalIn)-Sum(TotalOut) End " & _
                                      "From Transactions Where Acc1=N'" & Acc1 & "' And Acc2=N'" & Acc2 & "'", cnn4)
            Dim Balance As Double

            cnn4.Open()
            Balance = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Balance
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetBalanceAcc(ByVal Acc1 As String, ByVal Acc2 As String, ByVal Acc3 As String) As Double
        Try
            Dim cmd As New SqlCommand("Select Case When Sum(TotalIn)-Sum(TotalOut) Is Null Then 0 Else Sum(TotalIn)-Sum(TotalOut) End " & _
                                      "From Transactions Where Acc1=N'" & Acc1 & "' And Acc2=N'" & Acc2 & _
                                      "' And Acc3=N'" & Acc3 & "'", cnn4)
            Dim Balance As Double

            cnn4.Open()
            Balance = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Balance
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetBalanceAcc(ByVal Acc1 As String, ByVal Acc2 As String, ByVal Acc3 As String, ByVal Acc4 As String) As Double
        Try
            Dim cmd As New SqlCommand("Select Case When Sum(TotalIn)-Sum(TotalOut) Is Null Then 0 Else Sum(TotalIn)-Sum(TotalOut) End " & _
                                      "From Transactions Where Acc1=N'" & Acc1 & "' And Acc2=N'" & Acc2 & _
                                      "' And Acc3=N'" & Acc3 & "' And Acc4=N'" & Acc4 & "'", cnn4)
            Dim Balance As Double

            cnn4.Open()
            Balance = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Balance
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Sub PrintVoucher(ByVal Branch As String, ByVal MoveNo As Integer, ByVal Year As Integer)
        Try
            Dim dap As New SqlDataAdapter("select * From Transactions Where Branch=N'" & Branch & "' and MoveNo=" & MoveNo & " and Year(TransDate)=" & Year, cnn)
            Dim das As New DataSet

            dap.Fill(das, "Transactions")

            Dim rpt As New Voucher
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            rpt.Dispose()
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
        End Try
    End Sub

    Public Sub PrintPayBill(ByVal BranchBill As String, ByVal SNo As Integer)
        Try
            Dim dap As New SqlDataAdapter("Select * From Transactions Where Transtype=N'Pay' and PaperNo=" & SNo & _
                                          " and Branch=N'" & BranchBill & "' and TotalIn<>0", cnn)
            Dim das As New DataSet

            dap.Fill(das, "Transactions")

            Dim rpt As New PayBill
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            rpt.Dispose()
        Catch ex As Exception
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Sub PrintReceiptBill(ByVal BranchBill As String, ByVal SNo As Integer)
        Try
            Dim dap As New SqlDataAdapter("Select * From Transactions Where Transtype=N'Receipt' and PaperNo=" & SNo & _
                                          " and Branch=N'" & BranchBill & "' and TotalOut<>0", cnn)
            Dim das As New DataSet

            dap.Fill(das, "Transactions")

            Dim rpt As New ReceiptBill
            rpt.SetDataSource(das)
            rptViewer.CrystalReportViewer1.ReportSource = rpt
            rptViewer.CrystalReportViewer1.RefreshReport()
            rptViewer.ShowDialog()

            rpt.Dispose()
        Catch ex As Exception
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Public Function GetAccTransBalance(ByVal Acc1 As String, ByVal Acc2 As String, ByVal MinDate As DateTime, ByVal MaxDate As DateTime) As Double
        Try
            Dim cmd As New SqlCommand("Select Case When Sum(TotalIn)-Sum(TotalOut) Is Null Then 0 Else Sum(TotalIn)-Sum(TotalOut) End " & _
                                     "From Transactions Where Acc1=N'" & Acc1 & "' And Acc2=N'" & Acc2 & _
                                     "' and TransDate>N'" & MinDate & " 00:00:00' and TransDate<N'" & MaxDate & " 23:59:59'", cnn4)
            Dim Balance As Double

            cnn4.Open()
            Balance = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Balance
        Catch ex As Exception
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetAccClosingBalance(ByVal Acc1 As String, ByVal Acc2 As String, ByVal MaxDate As DateTime) As Double
        Try
            Dim cmd As New SqlCommand("Select Case When Sum(TotalIn)-Sum(TotalOut) Is Null Then 0 Else Sum(TotalIn)-Sum(TotalOut) End " & _
                                     "From Transactions Where Acc1=N'" & Acc1 & "' And Acc2=N'" & Acc2 & _
                                     "' and TransDate<N'" & MaxDate & " 23:59:59'", cnn4)
            Dim Balance As Double

            cnn4.Open()
            Balance = CDbl(cmd.ExecuteScalar)
            cnn4.Close()

            Return Balance
        Catch ex As Exception
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

    Public Function GetAccGroup(ByVal AccNo As String) As String
        Try
            Dim cmd As New SqlCommand("Select IsNull(Acc1,N'') From Acc Where AccNo=N'" & AccNo & "'", cnn4)
            Dim AccGroup As String

            cnn4.Open()
            AccGroup = CStr(cmd.ExecuteScalar)
            cnn4.Close()

            Return AccGroup
        Catch ex As Exception
            If cnn4.State = ConnectionState.Open Then
                cnn4.Close()
            End If
        End Try
    End Function

    Public Function GetAccNames(ByVal AccNo As String) As String()
        Try
            Dim cmd As New SqlCommand("Select Acc1,Acc2,Acc3,Acc4 From Acc Where AccNo=N'" & AccNo & "'", cnn)
            Dim Reader As SqlDataReader

            Dim AccNames(3) As String

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                AccNames(0) = Reader.Item(0)
                AccNames(1) = Reader.Item(1)
                AccNames(2) = Reader.Item(2)
                AccNames(3) = Reader.Item(3)
            End While
            cnn.Close()

            Return AccNames
        Catch ex As Exception
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Function

  End Module
